-------------------------------------------------------------
-- @name: histogram_and_cardinality
-- @author: dion cho
-- @description: test for histogram and cardinality
--------------------------------------------------------------

@capture_on

@version

-- create objects
drop table t1 purge;
create table t1(c1 int, c2 int);

-- c1 = frequency
-- c2 = height-balanced
insert into t1
select 1, mod(level, 2)+1
from dual
connect by level <= 10000
union all
select 2, mod(level, 300)+1
from dual
connect by level <= 1000
union all
select 3, mod(level, 50)+1
from dual
connect by level <= 100
union all
select 4, mod(level, 5)+1
from dual
connect by level <= 10
union all
select 5, mod(level, 1)+1
from dual
connect by level <= 1
;

commit;

begin
  dbms_stats.gather_table_stats(user, 't1', 
    method_opt=>'for all columns size skewonly');
end;
/

-- see how histogram is stored
@tab_stat t1


---------------------------------
-- cardinality problem
-- 1. frequency histogram
explain plan for
select * 
from t1
where c1 = 1
;

@plan

explain plan for
select * 
from t1
where c1 = 2
;

@plan

explain plan for
select * 
from t1
where c1 = :b1
;

@plan

explain plan for
select * 
from t1
where c1 > :b1
;

@plan


explain plan for
select * 
from t1
where c1 >= :b1
;

@plan

-- note!!! see disparity of cardinality estimation
-- of explain plan and runtime plan?
alter session set "_optim_peek_user_binds" = false;

var bx number;
exec :bx := 1;

select /*+ gather_plan_statistics */
  count(*)
from t1
where c1 >= :bx
;

@stat


explain plan for
select * 
from t1
where c1 between :b1 and :b2
;

@plan

explain plan for
select * 
from t1
where c1 = -1
;

@plan

explain plan for
select * 
from t1
where c1 < -1
;

@plan


-- cardinality problem
-- 2. height-balanced histogram
explain plan for
select * 
from t1
where c2 = 1
;

@plan

explain plan for
select * 
from t1
where c2 = 2
;

@plan

explain plan for
select * 
from t1
where c2 = 10
;

@plan

explain plan for
select * 
from t1
where c2 = :b1
;

@plan

explain plan for
select * 
from t1
where c2 > :b1
;

@plan


explain plan for
select * 
from t1
where c2 >= :b1
;

@plan

-- note!!! see "no" disparity of cardinality estimation
-- of explain plan and runtime plan?
alter session set "_optim_peek_user_binds" = false;

var bx number;
exec :bx := 1;

select /*+ gather_plan_statistics */
  count(*)
from t1
where c2 >= :bx
;

@stat


explain plan for
select * 
from t1
where c2 between :b1 and :b2
;

@plan

explain plan for
select * 
from t1
where c2 = -1
;

@plan

explain plan for
select * 
from t1
where c2 < -1
;

@plan


@capture_off
